﻿USE RENTIT11
GO

/* [START] Initialization */
IF OBJECT_ID('RENTAL') IS NOT NULL DROP TABLE RENTAL
GO
IF OBJECT_ID('MOVIEGENRE') IS NOT NULL DROP TABLE MOVIEGENRE
GO
IF OBJECT_ID('USERSESSION') IS NOT NULL DROP TABLE USERSESSION
GO
IF OBJECT_ID('PRICECATEGORY') IS NOT NULL DROP TABLE PRICECATEGORY
GO
IF OBJECT_ID('RATING') IS NOT NULL DROP TABLE RATING
GO
IF OBJECT_ID('USERACTIVATION') IS NOT NULL DROP TABLE USERACTIVATION
GO
IF OBJECT_ID('GENRE') IS NOT NULL DROP TABLE GENRE
GO
IF OBJECT_ID('MOVIE') IS NOT NULL DROP TABLE MOVIE
GO
IF OBJECT_ID('USERINFO') IS NOT NULL DROP TABLE USERINFO
GO
IF OBJECT_ID('SEARCHHISTORY') IS NOT NULL DROP TABLE SEARCHHISTORY
GO
IF EXISTS (SELECT 1 FROM SYS.FULLTEXT_CATALOGS WHERE name = 'FT_CATALOG') DROP FULLTEXT CATALOG FT_CATALOG
GO
CREATE FULLTEXT CATALOG FT_CATALOG AS DEFAULT
GO
/* [END] Initialization */


/* [START] Genre */
CREATE TABLE GENRE (
  id INT IDENTITY(1,1) NOT NULL,
  name VARCHAR(50) NOT NULL,
  CONSTRAINT PK_GENRE PRIMARY KEY(id)
)
GO

CREATE UNIQUE INDEX name ON GENRE (name)
GO
/* [END] Genre */


/* [START] UserInfo */
CREATE TABLE USERINFO (
  id INT IDENTITY(1,1) NOT NULL,
  name VARCHAR(50) NOT NULL,
  pwhash VARCHAR(32) NOT NULL,
  birthdate DATE NOT NULL,
  email VARCHAR(100) NOT NULL,
  salt VARCHAR(5) NOT NULL,
  isadmin TINYINT NOT NULL DEFAULT '0',
  CONSTRAINT PK_USERINFO PRIMARY KEY(id)
)
GO

CREATE UNIQUE INDEX email ON USERINFO (email)
GO

CREATE INDEX pwhash ON USERINFO (pwhash)
GO
/* [END] UserInfo */


/* [START] Movie */
CREATE TABLE MOVIE (
  id INT IDENTITY(1,1) NOT NULL,
  title VARCHAR(200) NOT NULL,
  movieyear SMALLINT NOT NULL,
  movielength SMALLINT NOT NULL,
  movielanguage VARCHAR(50) NOT NULL,
  pg VARCHAR(10) DEFAULT NULL,
  summary TEXT,
  hd TINYINT NOT NULL DEFAULT '0',
  releasedate DATE NOT NUll,
  imdb VARCHAR(20),
  CONSTRAINT PK_MOVIE PRIMARY KEY(id)
)
GO

CREATE UNIQUE INDEX titleyear ON MOVIE (title, movieyear)
GO

CREATE FULLTEXT INDEX ON MOVIE (title) KEY INDEX PK_MOVIE
GO

CREATE INDEX movieyear ON MOVIE (movieyear)
GO

CREATE INDEX hd ON MOVIE (hd)
GO

CREATE INDEX releasedate ON MOVIE (releasedate)
GO

CREATE UNIQUE INDEX imdb ON movie (imdb)
GO
/* [END] Movie */


/* [START] MovieGenre */
CREATE TABLE MOVIEGENRE (
  movieid INT NOT NULL,
  genreid INT NOT NULL,
  CONSTRAINT PK_MOVIEGENRE PRIMARY KEY(movieid, genreid),
  CONSTRAINT FK_MOVIEGENRE_MOVIE FOREIGN KEY (movieid) REFERENCES MOVIE(id) ON DELETE CASCADE,
  CONSTRAINT FK_MOVIEGENRE_GENRE FOREIGN KEY (genreid) REFERENCES GENRE(id) ON DELETE CASCADE
)
GO
/* [END] MovieGenre */


/* [START] Rental */
CREATE TABLE RENTAL (
  userid INT NOT NULL,
  movieid INT NOT NULL,
  rentdate DATETIME NOT NULL,
  seendate DATETIME,
  price NUMERIC NOT NULL,
  hd TINYINT NOT NULL DEFAULT 0,
  CONSTRAINT PK_RENTAL PRIMARY KEY(userid, movieid, rentdate),
  CONSTRAINT FK_RENTAL_USER FOREIGN KEY (userid) REFERENCES USERINFO(id) ON DELETE CASCADE,
  CONSTRAINT FK_RENTAL_MOVIE FOREIGN KEY (movieid) REFERENCES MOVIE(id) ON DELETE CASCADE
)
GO
/* [END] Rental */


/* [START] UserSession */
CREATE TABLE USERSESSION (
  userid INT NOT NULL,
  token VARCHAR(32) NOT NULL,
  lastaction INT NOT NULL,
  CONSTRAINT PK_USERSESSION PRIMARY KEY(userid, token),
  CONSTRAINT FK_USERSESSION_USER FOREIGN KEY (userid) REFERENCES USERINFO(id) ON DELETE CASCADE
)
GO
/* [END] UserSession */

/* [START] Rating */
CREATE TABLE RATING (
  userid INT NOT NULL,
  movieid INT NOT NULL,
  rating INT NOT NULL,
  comment TEXT,
  CONSTRAINT PK_RATING PRIMARY KEY (userid, movieid),
  CONSTRAINT FK_RATING_MOVIE FOREIGN KEY (movieid) REFERENCES MOVIE(id) ON DELETE CASCADE,
  CONSTRAINT FK_RATING_USER FOREIGN KEY (userid) REFERENCES USERINFO(id) ON DELETE CASCADE
)
GO
/* [END] Rating */


/* [START] UserActivation */
CREATE TABLE USERACTIVATION (
	userid INT NOT NULL,
	code VARCHAR(6) NOT NULL,
	CONSTRAINT PK_USERACTIVATION PRIMARY KEY (userid),
	CONSTRAINT FK_USERACTIVATION_USER FOREIGN KEY (userid) REFERENCES USERINFO(id) ON DELETE CASCADE
)
GO
/* [END] UserActivation */

/* [START] SearchHistory */
CREATE TABLE SEARCHHISTORY (
	id INT IDENTITY(1,1) NOT NULL,
	userid INT NOT NULL,
	searched VARBINARY(8000) NOT NULL,
	dateofsearch DATETIME NOT NULL,
	CONSTRAINT PK_SEARCHHISTORY PRIMARY KEY(id),
	CONSTRAINT FK_SEARCHHISTORY_USER FOREIGN KEY (userid) REFERENCES USERINFO(id) ON DELETE CASCADE
)
GO
/* [END] SearchHistory */